﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;

namespace StudentManagementSystem
{
    public class DatabaseManager
    {
        private string connectionString;

        public DatabaseManager(string dbPath)
        {
            connectionString = $"Data Source={dbPath};Version=3;";
            InitializeDatabase();
        }

        // 初始化数据库和表的方法保持不变
        private void InitializeDatabase()
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                // 创建学校表
                string createSchoolTableQuery = @"
                    CREATE TABLE IF NOT EXISTS Schools (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        Address TEXT
                    )";

                // 创建班级表
                string createClassroomTableQuery = @"
                    CREATE TABLE IF NOT EXISTS Classrooms (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        SchoolId INTEGER,
                        FOREIGN KEY(SchoolId) REFERENCES Schools(Id)
                    )";

                // 创建学生表
                string createStudentTableQuery = @"
                    CREATE TABLE IF NOT EXISTS Students (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Name TEXT NOT NULL,
                        Age INTEGER,
                        ClassroomId INTEGER,
                        FOREIGN KEY(ClassroomId) REFERENCES Classrooms(Id)
                    )";

                // 创建日志表
                string createLogTableQuery = @"
                    CREATE TABLE IF NOT EXISTS Logs (
                        Id INTEGER PRIMARY KEY AUTOINCREMENT,
                        Operation TEXT NOT NULL,
                        TableName TEXT NOT NULL,
                        RecordId INTEGER,
                        Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
                    )";

                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = createSchoolTableQuery;
                    command.ExecuteNonQuery();

                    command.CommandText = createClassroomTableQuery;
                    command.ExecuteNonQuery();

                    command.CommandText = createStudentTableQuery;
                    command.ExecuteNonQuery();

                    command.CommandText = createLogTableQuery;
                    command.ExecuteNonQuery();
                }
            }
        }

        // 添加学校
        public void AddSchool(School school)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Schools (Name, Address) VALUES (@Name, @Address)";
                    command.Parameters.AddWithValue("@Name", school.Name);
                    command.Parameters.AddWithValue("@Address", school.Address);
                    command.ExecuteNonQuery();

                    // 记录日志
                    LogOperation("INSERT", "Schools", -1);
                }
            }
        }

        // 获取学校列表
        public List<School> GetSchools()
        {
            var schools = new List<School>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand("SELECT * FROM Schools", connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            schools.Add(new School
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Address = reader["Address"].ToString()
                            });
                        }
                    }
                }
            }
            return schools;
        }

        // 添加班级
        public void AddClassroom(Classroom classroom)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Classrooms (Name, SchoolId) VALUES (@Name, @SchoolId)";
                    command.Parameters.AddWithValue("@Name", classroom.Name);
                    command.Parameters.AddWithValue("@SchoolId", classroom.SchoolId);
                    command.ExecuteNonQuery();

                    // 记录日志
                    LogOperation("INSERT", "Classrooms", -1);
                }
            }
        }

        // 获取班级列表
        public List<Classroom> GetClassrooms(int? schoolId = null)
        {
            var classrooms = new List<Classroom>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    if (schoolId.HasValue)
                    {
                        command.CommandText = "SELECT * FROM Classrooms WHERE SchoolId = @SchoolId";
                        command.Parameters.AddWithValue("@SchoolId", schoolId.Value);
                    }
                    else
                    {
                        command.CommandText = "SELECT * FROM Classrooms";
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            classrooms.Add(new Classroom
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                SchoolId = Convert.ToInt32(reader["SchoolId"])
                            });
                        }
                    }
                }
            }
            return classrooms;
        }

        // 添加学生
        public void AddStudent(Student student)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Students (Name, Age, ClassroomId) VALUES (@Name, @Age, @ClassroomId)";
                    command.Parameters.AddWithValue("@Name", student.Name);
                    command.Parameters.AddWithValue("@Age", student.Age);
                    command.Parameters.AddWithValue("@ClassroomId", student.ClassroomId);
                    command.ExecuteNonQuery();

                    // 记录日志
                    LogOperation("INSERT", "Students", -1);
                }
            }
        }

        // 获取学生列表
        public List<Student> GetStudents(int? classroomId = null)
        {
            var students = new List<Student>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    if (classroomId.HasValue)
                    {
                        command.CommandText = "SELECT * FROM Students WHERE ClassroomId = @ClassroomId";
                        command.Parameters.AddWithValue("@ClassroomId", classroomId.Value);
                    }
                    else
                    {
                        command.CommandText = "SELECT * FROM Students";
                    }

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student
                            {
                                Id = Convert.ToInt32(reader["Id"]),
                                Name = reader["Name"].ToString(),
                                Age = Convert.ToInt32(reader["Age"]),
                                ClassroomId = Convert.ToInt32(reader["ClassroomId"])
                            });
                        }
                    }
                }
            }
            return students;
        }

        // 记录日志
        private void LogOperation(string operation, string tableName, int recordId)
        {
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand(connection))
                {
                    command.CommandText = "INSERT INTO Logs (Operation, TableName, RecordId) VALUES (@Operation, @TableName, @RecordId)";
                    command.Parameters.AddWithValue("@Operation", operation);
                    command.Parameters.AddWithValue("@TableName", tableName);
                    command.Parameters.AddWithValue("@RecordId", recordId);
                    command.ExecuteNonQuery();
                }
            }
        }

        // 获取日志列表
        public List<(string Operation, string TableName, int RecordId, DateTime Timestamp)> GetLogs()
        {
            var logs = new List<(string, string, int, DateTime)>();
            using (var connection = new SQLiteConnection(connectionString))
            {
                connection.Open();
                using (var command = new SQLiteCommand("SELECT * FROM Logs ORDER BY Timestamp DESC", connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            logs.Add((
                                reader["Operation"].ToString(),
                                reader["TableName"].ToString(),
                                Convert.ToInt32(reader["RecordId"]),
                                Convert.ToDateTime(reader["Timestamp"])
                            ));
                        }
                    }
                }
            }
            return logs;
        }
    }
}